﻿Imports System
Imports System.Collections
Imports System.Configuration
Imports System.Data
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.HtmlControls
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Xml
Imports System.IO
Imports System.Text
Imports Microsoft.Office.Interop.Owc11
Partial Class WebForm_ManageSalesForecast_TransferPLV_INPUT2
    Inherits System.Web.UI.Page
    Private mySpreadSheet As New SpreadsheetClass() '暫存SpreadSheet

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        ScriptManager.GetCurrent(Me.Page).Services.Add(New ServiceReference("~/WebForm/ManageSalesForecast/SFService.asmx"))
        Me.BindOWC()
    End Sub

#Region "方法"
    Private Sub BindOWC()
        Me.CreateData()
        If Me.VControl = 0 Then
            Me.btnApply.Visible = False
        End If
    End Sub

    Private dtSFR As DataTable
    Private dtACTs(1) As DataTable   'Y,Y-1
    Public MonthLast As Integer
    Public Last_Data_Col As Integer
    Public Start_Data_Cell As String
    Public End_Data_Cell As String
    Private Sub CreateData()

        Me.GetSFReport()
        Me.GetSFReportACT()

        Dim Start_col As Integer = 4

        Dim EndYear As Integer
        MonthLast = 12
        If Me.FCSTMonth + 15 > 24 Then
            EndYear = CurrYear + 2 '到後年
            MonthLast = 36
        ElseIf Me.FCSTMonth + 15 > 12 Then
            EndYear = CurrYear + 1 '到明年
            MonthLast = 24
        End If

        Dim Title_row As Integer = 2
        Last_Data_Col = Start_col - 1 + (EndYear - Me.StartYear + 1) * 12
        End_Data_Cell = MyObj.ConvertToLetter(Last_Data_Col) & Me.dtSFR.Rows.Count + Title_row
        Dim myRange As String = ""

        Me.mySpreadSheet.Range("A2").Value = "Material"
        Me.mySpreadSheet.Range("B2").Value = "Description"
        Me.mySpreadSheet.Range("C2").Value = "Type"

        Dim curr_col As Integer = Start_col
        Dim MonthIndex As Integer
        Dim Flag As String

        '第一、二行
        For i As Integer = Me.StartYear To EndYear
            If i = Me.StartYear AndAlso dtSFR.Rows.Count > 0 Then
                Me.mySpreadSheet.Range("A3:A4").Value = MyObj.GetAreaCode() & dtSFR.Rows(0)("sapcode").ToString()
                Me.mySpreadSheet.Range("B3").Value = dtSFR.Rows(0)("Description").ToString()
                Me.mySpreadSheet.Range("B4").Value = dtSFR.Rows(0)("LocalDescription").ToString()
            End If

            '第二行日期格式
            myRange = MyObj.ConvertToLetter(curr_col) & Title_row.ToString() & ":" & MyObj.ConvertToLetter(curr_col + 12) & Title_row.ToString()
            Me.mySpreadSheet.Range(myRange).NumberFormat = "[ENG]mmm\-yy"
            For ii As Integer = 1 To 12
                myRange = MyObj.ConvertToLetter(curr_col) & Title_row.ToString()
                Select Case i
                    Case CurrYear - 1
                        MonthIndex = ii
                        Flag = "ACT"
                    Case CurrYear
                        MonthIndex = ii
                        If ii < Me.FCSTMonth Then
                            Flag = "ACT"
                        Else
                            Flag = "FCST"
                            '當年FCST月份變色
                            Me.mySpreadSheet.Range(myRange).Interior.Color = "#FFC000"
                        End If
                        If Me.Start_Data_Cell = "" AndAlso Flag = "FCST" Then
                            Me.Start_Data_Cell = MyObj.ConvertToLetter(curr_col) & (Title_row + 1).ToString()
                        End If
                    Case Else
                        MonthIndex = (i - CurrYear) * 12 + ii
                        Flag = "FCST"
                End Select
                Me.mySpreadSheet.Range(myRange).Value = i.ToString() & "/" & ii.ToString() & "/01"

                myRange = MyObj.ConvertToLetter(curr_col) & "1"
                With Me.mySpreadSheet.Range(myRange)
                    .HorizontalAlignment = XlHAlign.xlHAlignCenter
                    .Value = Flag
                    .Font.Italic = True
                    .Font.Color = "red"
                End With

                '資料行
                For iii As Integer = 0 To dtSFR.Rows.Count - 1
                    myRange = MyObj.ConvertToLetter(curr_col) & (3 + iii).ToString()
                    If Flag = "ACT" Then
                        Me.mySpreadSheet.Range(myRange).Value = dtACTs(i - Me.StartYear).Rows(iii)("Mon" & MonthIndex).ToString()
                    Else
                        Me.mySpreadSheet.Range(myRange).Value = dtSFR.Rows(iii)("Mon" & MonthIndex).ToString()
                    End If
                Next

                curr_col += 1
            Next
        Next

        '資料行
        For i As Integer = 0 To dtSFR.Rows.Count - 1
            Me.mySpreadSheet.Range("A" & (3 + i).ToString()).Value = MyObj.GetAreaCode() & dtSFR.Rows(i)("sapcode").ToString()
            Me.mySpreadSheet.Range("B" & (3 + i).ToString()).Value = dtSFR.Rows(i)("Description").ToString()
            Me.mySpreadSheet.Range("C" & (3 + i).ToString()).Value = dtSFR.Rows(i)("Text").ToString()

            '最後面加上SKutype
            myRange = MyObj.ConvertToLetter(Last_Data_Col + 1) & (3 + i).ToString()
            Me.mySpreadSheet.Range(myRange).Value = dtSFR.Rows(i)("SkuType").ToString()
        Next

        '左邊標題區
        myRange = "A:" & MyObj.ConvertToLetter(Start_col - 1)
        With Me.mySpreadSheet.Range(myRange)
            .Font.Name = "Arial Unicode MS"
            .Font.Size = "8"
            .HorizontalAlignment = XlHAlign.xlHAlignLeft
            .VerticalAlignment = XlVAlign.xlVAlignCenter
        End With
        '右邊資料區
        myRange = MyObj.ConvertToLetter(Start_col) & ":" & MyObj.ConvertToLetter(Last_Data_Col)
        With Me.mySpreadSheet.Range(myRange)
            .Font.Name = "Arial Unicode MS"
            .Font.Size = "8"
            .HorizontalAlignment = XlHAlign.xlHAlignRight
            .VerticalAlignment = XlVAlign.xlVAlignCenter
        End With

        '標題列
        myRange = "A" & Title_row.ToString() & ":" & MyObj.ConvertToLetter(Last_Data_Col) & Title_row.ToString()
        With Me.mySpreadSheet.Range(myRange)
            .HorizontalAlignment = XlHAlign.xlHAlignCenter
            .VerticalAlignment = XlVAlign.xlVAlignCenter
        End With
        '資料
        Dim dataCount As Integer = Title_row + 1
        myRange = MyObj.ConvertToLetter(Start_col) & dataCount.ToString() & ":" & MyObj.ConvertToLetter(Last_Data_Col) & (dataCount + dtSFR.Rows.Count).ToString()
        With Me.mySpreadSheet.Range(myRange)
            .NumberFormat = "#,##0_);[Red]\(#,##0\)"
        End With
        'Total
        Dim TotalCount As Integer = Me.dtSFR.Rows.Count + Title_row + 1
        If TotalCount > 3 Then
            Me.mySpreadSheet.Range("C" & TotalCount.ToString()).Value = "TOTAL："
            Me.mySpreadSheet.Range("C" & TotalCount.ToString()).Locked = True
            Dim MyFormula As String = ""
            MyFormula = "=SUM(R[-" & Me.dtSFR.Rows.Count.ToString() & "]C:R[-1]C)"

            myRange = "D" & TotalCount.ToString() & ":" & MyObj.ConvertToLetter(Last_Data_Col) & TotalCount.ToString()
            Me.mySpreadSheet.Range(myRange).Formula = MyFormula
        End If
        Me.mySpreadSheet.Range("A:A").ColumnWidth = 45
        Me.mySpreadSheet.Range("B:B").ColumnWidth = 115.5
        Me.mySpreadSheet.Range("C:C").ColumnWidth = 75

        myRange = MyObj.ConvertToLetter(Last_Data_Col + 1) & ":" & MyObj.ConvertToLetter(Last_Data_Col + 1)
        With Me.mySpreadSheet.Range(myRange)
            .EntireColumn.Hidden = True
        End With

        mySpreadSheet.ActiveWorkbook.Worksheets("sheet1").Activate()
        mySpreadSheet.ActiveWorkbook.Worksheets("sheet2").Delete()
        mySpreadSheet.ActiveWorkbook.Worksheets("sheet3").Delete()
        XmlHiddenField.Value = mySpreadSheet.XMLData

    End Sub

    Private Sub GetSFReport()

        Dim sql As New MyStringBuilder
        Dim dat As New clsData
        Dim tablename As String = Me.HeaderTB & "SFReport" & Session(Me.SessionID & "SF_MonthCode")

        sql.Length = 0
        sql.AppendFormat("SELECT A.*,C.Description,C.LocalDescription,B.Text FROM {0} A ", tablename)
        sql.AppendFormat(" LEFT JOIN CodeMapping B ON A.SkuType = B.Value ")
        sql.AppendFormat(" LEFT JOIN ProductData C ON A.SapCode = C.SapCode ")
        sql.AppendFormat("join EmployeeHierarchyMapping EHM on C.ProdHierarchy=EHM.AuthProdHierarchy and EHM.EmployeeID='{0}' ", clsAuth.GetUserID())
        sql.AppendFormat("and EHM.ViewPoint='{0}' and EHM.Division='{1}' ", Me.ViewPoint, Me.Division)
        sql.AppendFormat(" WHERE B.TableName='Product' and B.ColumnName = 'MPStatus_PLV' ")
        sql.AppendFormat("      and A.VERSION1 = '{0}'  ", Me.Version1)
        sql.AppendFormat("      and A.ChannelID = '{0}' ", Me.ChannelID)
        'sql.AppendFormat("      and C.Active = 'Y' ")
        Dim SapCodeDT As DataTable = TryCast(Me.Session(Me.SessionID & "SapCodeDT"), DataTable)
        If SapCodeDT.Rows.Count > 0 Then
            For j As Integer = 0 To SapCodeDT.Rows.Count - 1
                If j = 0 Then
                    sql.AppendFormat("AND A.SapCode in('{0}'", SapCodeDT.Rows(j)("SapCode"))
                Else
                    sql.AppendFormat(",'{0}'", SapCodeDT.Rows(j)("SapCode"))
                End If
                If j = SapCodeDT.Rows.Count - 1 Then
                    sql.AppendFormat(")")
                End If
            Next
        Else
            sql.AppendFormat("AND 1=2 ")
        End If

        If Me.MaterialType = "AllPLV" Then
            sql.AppendFormat("AND A.MaterialType not in('N1','B1','R1','B2') ")
        ElseIf Me.MaterialType = "AllFG" Then
            sql.AppendFormat("AND A.MaterialType in('N1','R1') ")
        Else
            sql.AppendFormat("AND A.MaterialType='{0}' ", Me.MaterialType)
        End If

        If Me.PLVType <> "" Then
            sql.AppendFormat(" and A.SkuType='{0}' ", Me.PLVType)
        End If
        sql.AppendFormat(" and ( ")
        For i As Integer = 1 To 36
            If i = 1 Then
                sql.AppendFormat(" Mon{0} IS NOT NULL ", i)
            Else
                sql.AppendFormat(" OR Mon{0} IS NOT NULL ", i)
            End If
        Next
        sql.AppendFormat(" ) ")
        sql.AppendFormat(" ORDER BY A.SapCode,B.Text  ")
        Me.dtSFR = dat.GetData(sql)
    End Sub

    Private Sub GetSFReportACT()
        Dim sql As New MyStringBuilder
        Dim dat As New clsData
        Dim tablename As String

        Dim mons As New List(Of String)
        For i As Integer = 1 To 12
            mons.Add(i)
        Next

        Dim TableConunt As Integer = 0
        For i As Integer = CurrYear - 1 To CurrYear
            tablename = "SFReport" & i & "A"

            sql.Length = 0
            sql.AppendFormat("SELECT A.SapCode,A.SkuType,C.Description,C.LocalDescription,B.Text,{0} ", MyObj.JoinFormat(",", "SFA.Mon{0}", mons))
            sql.AppendFormat("FROM {0} A ", Me.HeaderTB & "SFReport" & Session(Me.SessionID & "SF_MonthCode"))
            sql.AppendFormat(" LEFT JOIN CodeMapping B ON A.SkuType = B.Value ")
            sql.AppendFormat(" LEFT JOIN ProductData C ON A.SapCode = C.SapCode ")
            sql.AppendFormat(" LEFT JOIN {0} SFA ON A.SkuType = SFA.SkuType ", tablename)
            sql.AppendFormat("      and SFA.ChannelID = '{0}' ", Me.ChannelID)
            sql.AppendFormat("      and SFA.SapCode = A.SapCode ")
            sql.AppendFormat("join EmployeeHierarchyMapping EHM on C.ProdHierarchy=EHM.AuthProdHierarchy and EHM.EmployeeID='{0}' ", clsAuth.GetUserID())
            sql.AppendFormat("and EHM.ViewPoint='{0}' and EHM.Division='{1}' ", Me.ViewPoint, Me.Division)
            sql.AppendFormat(" WHERE B.TableName='Product' and B.ColumnName = 'MPStatus_PLV' ")
            sql.AppendFormat("      and A.VERSION1 = '{0}'  ", Me.Version1)
            sql.AppendFormat("      and A.ChannelID = '{0}' ", Me.ChannelID)
            'sql.AppendFormat("      and C.Active = 'Y' ")
            Dim SapCodeDT As DataTable = TryCast(Me.Session(Me.SessionID & "SapCodeDT"), DataTable)
            If SapCodeDT.Rows.Count > 0 Then
                For j As Integer = 0 To SapCodeDT.Rows.Count - 1
                    If j = 0 Then
                        sql.AppendFormat("AND A.SapCode in('{0}'", SapCodeDT.Rows(j)("SapCode"))
                    Else
                        sql.AppendFormat(",'{0}'", SapCodeDT.Rows(j)("SapCode"))
                    End If
                    If j = SapCodeDT.Rows.Count - 1 Then
                        sql.AppendFormat(")")
                    End If
                Next
            Else
                sql.AppendFormat("AND 1=2 ")
            End If

            If Me.MaterialType = "AllPLV" Then
                sql.AppendFormat("AND A.MaterialType not in('N1','B1','R1','B2') ")
            ElseIf Me.MaterialType = "AllFG" Then
                sql.AppendFormat("AND A.MaterialType in('N1','R1') ")
            Else
                sql.AppendFormat("AND A.MaterialType='{0}' ", Me.MaterialType)
            End If

            If Me.PLVType <> "" Then
                sql.AppendFormat(" and A.SkuType='{0}' ", Me.PLVType)
            End If
            sql.AppendFormat(" and ( ")
            For ii As Integer = 1 To 36
                If ii = 1 Then
                    sql.AppendFormat(" A.Mon{0} IS NOT NULL ", ii)
                Else
                    sql.AppendFormat(" OR A.Mon{0} IS NOT NULL ", ii)
                End If
            Next
            sql.AppendFormat(" ) ")
            sql.AppendFormat(" ORDER BY A.SapCode,B.Text  ")
            'MyObj.WriteLog(sql.ToString())
            Me.dtACTs(TableConunt) = dat.GetData(sql)
            TableConunt += 1
        Next
    End Sub
#End Region

    Public ReadOnly Property StartYear() As Integer
        Get
            Return Me.CurrYear - 1
        End Get
    End Property

#Region "屬性"
    Protected ReadOnly Property Division() As String
        Get
            Return Session(Me.SessionID & "SF_Division")
        End Get
    End Property

    Public ReadOnly Property PLVType()
        Get
            Return Me.Request.QueryString.Item("PLVType")
        End Get
    End Property

    Public ReadOnly Property Version1() As String
        Get
            Return "M"
        End Get
    End Property

    Public ReadOnly Property ChannelID() As String
        Get
            Return ""
        End Get
    End Property

    Protected ReadOnly Property MaterialType() As String
        Get
            Return Session(Me.SessionID & "SF_MMPP")
        End Get
    End Property

    Protected ReadOnly Property CurrYear() As Integer
        Get
            Return Session(Me.SessionID & "SF_Year")
        End Get
    End Property

    Protected ReadOnly Property FCSTMonth() As String
        Get
            Return Session(Me.SessionID & "SF_FCSTMonth")
        End Get
    End Property

    Public ReadOnly Property SessionID() As String
        Get
            Return Me.Request.QueryString.Item("SessionID")
        End Get
    End Property

    Public ReadOnly Property VControl() As String
        Get
            Return Me.Request.QueryString.Item("VControl")
        End Get
    End Property

    Public ReadOnly Property Locked() As Boolean
        Get
            Return Session(Me.SessionID & "SF_Lock")
        End Get
    End Property
#End Region

    Public ReadOnly Property HeaderTB() As String
        Get
            Return Session(Me.SessionID & "SF_HeaderTB")
        End Get
    End Property

    Public ReadOnly Property ViewPoint() As String
        Get
            Return Session(Me.SessionID & "SF_ViewPoint")
        End Get
    End Property
End Class
